package com.example.yrdev.ordering_system.Database;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import com.example.yrdev.ordering_system.Common.Setting;

import com.example.yrdev.ordering_system.Model.CardRecord;
import com.example.yrdev.ordering_system.Model.CardTypeInfo;
import com.example.yrdev.ordering_system.Model.Good;
import com.example.yrdev.ordering_system.Model.GoodType;

import com.example.yrdev.ordering_system.Model.Operator;
import com.example.yrdev.ordering_system.Model.PurchaseReport;
import com.example.yrdev.ordering_system.Utils.GlobalFunction;

import java.util.ArrayList;

/**
 * Created by luke on 9/21/16.
 */
public class DingHanDB extends SQLiteOpenHelper {
    public static final String DATABASE_NAME = "DingHan.db";


   private static final int DATABASE_VERSION = 1;
    private static DingHanDB dingHanDB=null;



    public static DingHanDB getInstance(Context context){
        if (dingHanDB==null)
            dingHanDB=new DingHanDB(context);

        return dingHanDB;

    }
   public DingHanDB(Context context){

      super(context,DATABASE_NAME,null,DATABASE_VERSION);


  }

    @Override
    public void onCreate(SQLiteDatabase db) {

        db.execSQL(CardRecord.DH_CREATE_TABLE);
        db.execSQL(PurchasedItemTB.DH_CREATE_TABLE);
        db.execSQL(RefundGoodTB.DH_CREATE_TABLE);
        db.execSQL(MenuItemTB.DH_CREATE_TABLE);
        db.execSQL(Good.DH_CREATE_TABLE);
        db.execSQL(GoodType.DH_CREATE_TABLE);
        db.execSQL(Operator.DH_CREATE_TABLE);
        db.execSQL(PurchaseReport.DH_CREATE_TABLE);
        db.execSQL(Setting.DH_CREATE_TABLE);
        db.execSQL(CardTypeInfo.DH_CREATE_TABLE);


    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

        db.execSQL("DROP TABLE IF EXISTS " + CardRecord.DH_TABLE_NAME);
        db.execSQL("DROP TABLE IF EXISTS " + PurchasedItemTB.DH_TABLE_NAME);
        db.execSQL("DROP TABLE IF EXISTS " + RefundGoodTB.DH_TABLE_NAME);
        db.execSQL("DROP TABLE IF EXISTS " + MenuItemTB.DH_TABLE_NAME);
        db.execSQL("DROP TABLE IF EXISTS " + Good.DH_TABLE_NAME);
        db.execSQL("DROP TABLE IF EXISTS " + GoodType.DH_TABLE_NAME);
        db.execSQL("DROP TABLE IF EXISTS " + Operator.DH_TABLE_NAME);
        db.execSQL("DROP TABLE IF EXISTS " + PurchaseReport.DH_TABLE_NAME);
        db.execSQL("DROP TABLE IF EXISTS " + Setting.DH_TABLE_NAME);
        db.execSQL("DROP TABLE IF EXISTS " + CardTypeInfo.DH_TABLE_NAME);


        onCreate(db);

    }

  private   ContentValues contentValuesFromObj(Object object){
        ContentValues contentValues=new ContentValues();

        if (object.getClass()== CardRecord.class){

            CardRecord record=(CardRecord)object;
           String httpDateTime= GlobalFunction.httpLatestReqTime();

            contentValues.put(CardRecord.DH_Card_TicketID,record.getTicketID());
            contentValues.put(CardRecord.DH_Card_No,record.getCardNo());
            contentValues.put(CardRecord.DH_Card_UsageCount,record.getUsageCount());
            contentValues.put(CardRecord.DH_Card_purchaseAmount,record.getPurchaseAmount());
            contentValues.put(CardRecord.DH_Card_remain,record.getRemain());
            contentValues.put(CardRecord.DH_Card_discountRate,record.getDiscountRate());
            contentValues.put(CardRecord.DH_Card_Date,record.getUsageDate());
            contentValues.put(CardRecord.DH_Card_HttpDateTime,httpDateTime);
            contentValues.put(CardRecord.DH_Card_DeviceNo,record.getDeviceNo());
            contentValues.put(CardRecord.DH_Card_Operator,record.getOperator());
            contentValues.put(CardRecord.DH_Card_ReportType,record.getReportType());

            contentValues.put(CardRecord.DH_Card_Name,record.getCardName());
            contentValues.put(CardRecord.DH_Card_RefundIndex,record.getRefundIndex());

            GlobalFunction.printTextByTag(record.getOperator());

        }else  if (object.getClass()== PurchasedItemTB.Item.class){

            PurchasedItemTB.Item item=(PurchasedItemTB.Item)object;
            GlobalFunction.printTextByTag("recordID1:"+item.item_TicketID);

            contentValues.put(PurchasedItemTB.item_CardID,item.card_ID);
            contentValues.put(PurchasedItemTB.item_TicketID,item.item_TicketID);
            contentValues.put(PurchasedItemTB.item_ID,item.item_ID);
            GlobalFunction.printTextByTag("price"+item.getTotalPrice());
            contentValues.put(PurchasedItemTB.item_totalPrice,item.getTotalPrice());
            contentValues.put(PurchasedItemTB.item_name,item.item_name);
            contentValues.put(PurchasedItemTB.item__amount,item.item_amount);
            contentValues.put(PurchasedItemTB.item_payMode,item.item_payMode);
            contentValues.put(PurchasedItemTB.item_purchDate,item.getItem_purchDate());
            contentValues.put(PurchasedItemTB.item_purchTime,item.getItem_purchTime());
            contentValues.put(PurchasedItemTB.item_SaleCost,item.saleCost);
            GlobalFunction.printTextByTag("insert " + item.item_TicketID + " "+item.item_ID );

        }else if (object.getClass()== PurchaseReport.class){


            PurchaseReport record=(PurchaseReport) object;
            contentValues.put(PurchaseReport.item_ticketID,record.getTicketID());
            contentValues.put(PurchaseReport.item_commodityID,record.getCommodityID());
            contentValues.put(PurchaseReport.item_commodityName,record.getCommodityName());
            contentValues.put(PurchaseReport.item_volume,record.getVolume());
            contentValues.put(PurchaseReport.item_primeCost,record.getPrimeCost());
            contentValues.put(PurchaseReport.item_saleCost,record.getSaleCost());
            contentValues.put(PurchaseReport.item_cost,record.getCost());
            contentValues.put(PurchaseReport.item_restoredNum,record.getRestoredNum());
            contentValues.put(PurchaseReport.item_discount,record.getDisCount());
            contentValues.put(PurchaseReport.item_dateTime,record.getDateTime());
            contentValues.put(PurchaseReport.item_dateTimeCopy,record.getDateTimeCopy());
            contentValues.put(PurchaseReport.item_employeeID,record.getEmployeeID());
            contentValues.put(PurchaseReport.item_payMode,record.getPayMode());
            contentValues.put(PurchaseReport.item_vipCardId,record.getVipCardID());
            contentValues.put(PurchaseReport.item_date,record.getDate());

          }else if(object.getClass()==RefundGoodTB.Item.class){

            RefundGoodTB.Item item=(RefundGoodTB.Item)object;

            contentValues.put(RefundGoodTB.item_TicketID,item.item_TicketID);
            contentValues.put(RefundGoodTB.item_ID,item.item_ID);
            contentValues.put(RefundGoodTB.item_name,item.item_name);
            contentValues.put(RefundGoodTB.item__amount,item.item_amount);
            contentValues.put(RefundGoodTB.item_payMode,item.item_payMode);
            contentValues.put(RefundGoodTB.item_purchDate,item.item_purchDate);
            contentValues.put(RefundGoodTB.item_refundDate,item.getItem_refundDate());
            contentValues.put(RefundGoodTB.item_SaleCost,item.saleCost);
            contentValues.put(RefundGoodTB.item_PurchaseVolume,item.purchaseVolume);
            contentValues.put(RefundGoodTB.item_PrimeCost,item.primeCost);
            contentValues.put(RefundGoodTB.item_Discount,item.discountRate);
            contentValues.put(RefundGoodTB.item_restoredNum,item.restoredNum);
            contentValues.put(RefundGoodTB.item_httpDateTimeCopy,item.httpDateTime);
            contentValues.put(RefundGoodTB.item_EmployeID,item.employeeID);
            contentValues.put(RefundGoodTB.item_CardID,item.card_ID);


        }else if (object.getClass()== Good.class){

            Good record=(Good) object;
            contentValues.put(Good.item_barcode,record.getBarcode());
            contentValues.put(Good.item_discountable,record.getDisoountable());
            contentValues.put(Good.item_id,record.getId());
            contentValues.put(Good.item_kind,record.getKind());
            contentValues.put(Good.item_name,record.getName());
            contentValues.put(Good.item_pinyinID,record.getPinyinID());
            contentValues.put(Good.item_priceout,record.getPriceOut());
            contentValues.put(Good.item_priceIn,record.getPriceIn());
            contentValues.put(Good.item_warning,record.getWarning());
            contentValues.put(Good.item_storedNumber,record.getStoredNumber());

        }else if(object.getClass()==GoodType.class){
            GoodType record=(GoodType) object;
            contentValues.put(GoodType.item_id,record.getId());
            contentValues.put(GoodType.item_name,record.getName());

        }else if(object.getClass()==Operator.class){

            Operator operator=(Operator)object;
            contentValues.put(Operator.operator_operCardNo,operator.getOperCardNo());
            contentValues.put(Operator.operator_employeeID,operator.getEmployeeID());
            contentValues.put(Operator.operator_password,operator.getPassword());
            contentValues.put(Operator.operator_authority,operator.getAuthority());

        }else if (object.getClass()==CardTypeInfo.class){

            CardTypeInfo cardTypeInfo=(CardTypeInfo)object;
            contentValues.put(CardTypeInfo.DH_Card_ID,cardTypeInfo.id);
            contentValues.put(CardTypeInfo.DH_Card_Name,cardTypeInfo.name);
            contentValues.put(CardTypeInfo.DH_Card_addRatio,cardTypeInfo.addRatio);
            contentValues.put(CardTypeInfo.DH_Card_Discount,cardTypeInfo.discount);


        }


        return contentValues;

    }

    public ArrayList<GoodType> getGoodTypeList(){
        SQLiteDatabase db=this.getReadableDatabase();

        Cursor res=db.rawQuery("Select * from "+ GoodType.DH_TABLE_NAME,null);
        ArrayList <GoodType> temp=new ArrayList<>();


        if (res.moveToFirst()){
            GlobalFunction.printTextByTag("q succ");
            while (!res.isAfterLast()){

                GoodType goodType=new GoodType(res.getString(res.getColumnIndex(GoodType.item_id))
                        ,res.getString(res.getColumnIndex(GoodType.item_name)));
                temp.add(goodType);

                res.moveToNext();

            }


        }else {
            GlobalFunction.printTextByTag("query fail");
        }
        return temp;


    }


    public   void updateGoodStoredNum(Good good){//更新长裤商品数量
        SQLiteDatabase db=this.getReadableDatabase();


        String query="UPDATE "+good.DH_TABLE_NAME+ " SET "+ good.item_storedNumber + "="+good.getStoredNumber()+
                " WHERE "+good.item_id+"="+good.getId() ;

        db.execSQL(query);
        db.close();

    }
    public ArrayList<Operator> getOperatorList(){
        SQLiteDatabase db=this.getReadableDatabase();

        Cursor res=db.rawQuery("Select * from "+ Operator.DH_TABLE_NAME,null);
        ArrayList <Operator> temp=new ArrayList<>();


        if (res.moveToFirst()){
            GlobalFunction.printTextByTag("q succ");
            while (!res.isAfterLast()){
                Operator operator=new Operator(res);


                temp.add(operator);
//
                res.moveToNext();

            }

            res.close();

        }else {

            GlobalFunction.printTextByTag("q fail");
        }

        return temp;


    }
    public ArrayList<Good> getGoodDetailList(){
        SQLiteDatabase db=this.getReadableDatabase();

        Cursor res=db.rawQuery("Select * from "+ Good.DH_TABLE_NAME,null);
        ArrayList <Good> temp=new ArrayList<>();


        if (res.moveToFirst()){
            GlobalFunction.printTextByTag("q succ");
            while (!res.isAfterLast()){

                Good good=new Good();
                good.setId(res.getString(res.getColumnIndex(Good.item_id)));
                good.setName(res.getString(res.getColumnIndex(Good.item_name)));
                good.setStoredNumber(res.getString(res.getColumnIndex(Good.item_storedNumber)));
                good.setPriceOut(res.getString(res.getColumnIndex(Good.item_priceout)));
                good.setPinyinID(res.getString(res.getColumnIndex(Good.item_pinyinID)));
                good.setKind(res.getString(res.getColumnIndex(Good.item_kind)));
                good.setPriceIn(res.getString(res.getColumnIndex(Good.item_priceIn)));
                // good.setId(res.getString(res.getColumnIndex(Good.item_id)));
                good.setDisoountable(res.getString(res.getColumnIndex(Good.item_discountable)));
                temp.add(good);

                res.moveToNext();

            }


        }else {
            GlobalFunction.printTextByTag("q fail");
        }
        return temp;


    }


    public Good getCoresspondingGoodDetail(String goodID){//获取对应商品

        SQLiteDatabase db=this.getReadableDatabase();

        Cursor res=db.rawQuery("Select * from "+ Good.DH_TABLE_NAME + " WHERE " + Good.item_id+"="+goodID,null);


        Good good=new Good();

        if (res.moveToFirst()){
            GlobalFunction.printTextByTag("q succ");
            while (!res.isAfterLast()){


                good.setId(res.getString(res.getColumnIndex(Good.item_id)));
                good.setName(res.getString(res.getColumnIndex(Good.item_name)));
                good.setStoredNumber(res.getString(res.getColumnIndex(Good.item_storedNumber)));
                good.setPriceOut(res.getString(res.getColumnIndex(Good.item_priceout)));
                good.setPinyinID(res.getString(res.getColumnIndex(Good.item_pinyinID)));
                good.setKind(res.getString(res.getColumnIndex(Good.item_kind)));
                good.setPriceIn(res.getString(res.getColumnIndex(Good.item_priceIn)));
                // good.setId(res.getString(res.getColumnIndex(Good.item_id)));
                good.setDisoountable(res.getString(res.getColumnIndex(Good.item_discountable)));


    res.moveToNext();


            }

    res.close();
        }else {

            GlobalFunction.printTextByTag("q fail");

        }

        return good;

    }


    public void insert(String  tableName,Object object){

        try {

            SQLiteDatabase db=this.getWritableDatabase();

            db.beginTransaction();

            db.insert(tableName, null, contentValuesFromObj(object));

            db.setTransactionSuccessful();
            db.endTransaction();



        }catch (Exception e){

        GlobalFunction.printTextByTag("insert fail");

        }

    }

    public void deleteAllData(String TABLE_NAME){//删除所有记录
        SQLiteDatabase db=this.getWritableDatabase();


        db.execSQL("delete from "+ TABLE_NAME);
    }



    public ArrayList<PurchaseReport> getPurchaseReport(){
        SQLiteDatabase db=this.getReadableDatabase();

        Cursor res=db.rawQuery("Select * from "+ PurchaseReport.DH_TABLE_NAME  + " AND " + PurchaseReport.item_employeeID + "=" + Operator.getIntance().getEmployeeID(),null);
        ArrayList <PurchaseReport> temp=new ArrayList<>();


        if (res.moveToFirst()){
            GlobalFunction.printTextByTag("q succ");
            while (!res.isAfterLast()){
                PurchaseReport report=new PurchaseReport();
                report.setTicketID(res.getString(res.getColumnIndex(PurchaseReport.item_ticketID)));
                report.setCommodityID(res.getString(res.getColumnIndex(PurchaseReport.item_commodityID)));
                report.setVolume(res.getInt(res.getColumnIndex(PurchaseReport.item_volume)));
                report.setPrimeCost(res.getString(res.getColumnIndex(PurchaseReport.item_primeCost)));
                report.setSaleCost(res.getString(res.getColumnIndex(PurchaseReport.item_saleCost)));
                report.setCost(res.getString(res.getColumnIndex(PurchaseReport.item_cost)));
                report.setRestoredNum(res.getInt(res.getColumnIndex(PurchaseReport.item_restoredNum)));
                report.setDisCount(res.getString(res.getColumnIndex(PurchaseReport.item_discount)));
                report.setDateTime(res.getString(res.getColumnIndex(PurchaseReport.item_dateTime)));
                report.setDateTimeCopy(res.getString(res.getColumnIndex(PurchaseReport.item_dateTimeCopy)));
                report.setEmployeeID(res.getString(res.getColumnIndex(PurchaseReport.item_employeeID)));
                report.setCommodityName(res.getString(res.getColumnIndex(PurchaseReport.item_commodityName)));
                report.setPayMode(res.getInt(res.getColumnIndex(PurchaseReport.item_payMode)));
                report.setVipCardID(res.getString(res.getColumnIndex(PurchaseReport.item_vipCardId)));
                report.setDate(res.getString(res.getColumnIndex(PurchaseReport.item_date)));

//                GoodType goodType=new GoodType(res.getString(res.getColumnIndex(GoodType.item_id))
//                        ,res.getString(res.getColumnIndex(GoodType.item_name)));
                temp.add(report);
//
                res.moveToNext();

            }

            res.close();
        }else {
            GlobalFunction.printTextByTag("q fail");
        }

        return temp;


    }



    public   void changeCardReportUploadMark(CardRecord record){//会员明细上传表示
        SQLiteDatabase db=this.getReadableDatabase();


        String query="UPDATE "+ CardRecord.DH_TABLE_NAME+ " SET "+ CardRecord.DH_Card_uploadMark + "="+ record.getUploadMark()+
                " WHERE "+ CardRecord.DH_Card_No+"="+record.getCardNo()+" AND "+ CardRecord.DH_Card_UsageCount+"="+record.getUsageCount()
                + " AND " + CardRecord.DH_Card_Operator+ "=" + Operator.getIntance().getEmployeeID();


        db.execSQL(query);
        db.close();

    }
    public   void changeReportUploadMark(PurchaseReport report){//消费宝宝
        SQLiteDatabase db=this.getReadableDatabase();


        String query="UPDATE "+ PurchaseReport.DH_TABLE_NAME+ " SET "+ PurchaseReport.item_uploadMark + "="+report.getUploadMark()+
                " WHERE "+ PurchaseReport.item_ticketID+"="+report.getTicketID()+" AND "+ PurchaseReport.item_commodityID+"="+report.getCommodityID()
                + " AND " + PurchaseReport.item_employeeID + "=" + Operator.getIntance().getEmployeeID();


        db.execSQL(query);
        db.close();

    }

    public   void changeRefundReportUploadMark(RefundGoodTB.Item record){//退货报表上
        SQLiteDatabase db=this.getReadableDatabase();


        String query="UPDATE "+ RefundGoodTB.DH_TABLE_NAME+ " SET "+ RefundGoodTB.item_uploadMark + "="+ record.getUploadMark()+
                " WHERE "+ RefundGoodTB.item_TicketID+"="+record.item_TicketID + " AND " + PurchaseReport.item_employeeID + "=" + Operator.getIntance().getEmployeeID();



        db.execSQL(query);
        db.close();

    }

    public CardRecord getCorresPondReport(PurchaseReport purchaseItem){

        CardRecord record=null;
        SQLiteDatabase db=this.getReadableDatabase();
        GlobalFunction.printTextByTag(purchaseItem.getVipCardID()+":"+purchaseItem.getTicketID());
        Cursor res=db.query(CardRecord.DH_TABLE_NAME,null,  CardRecord.DH_Card_No + " = ?" +" AND "+ CardRecord.DH_Card_TicketID+" = ?"
                        + " AND " + CardRecord.DH_Card_Operator + "= ?" ,
                new String[]{purchaseItem.getVipCardID(),purchaseItem.getTicketID(), Operator.getIntance().getEmployeeID()},null,null,null);

        if (res.moveToFirst()){
            record  =new CardRecord(res.getString(res.getColumnIndex(CardRecord.DH_Card_No)))  ;
            record.setId(res.getInt(res.getColumnIndex(CardRecord.DH_Card_ID)));
            record.setUsageCount(res.getInt(res.getColumnIndex(CardRecord.DH_Card_UsageCount)));
            record.setPurchaseAmount(res.getDouble(res.getColumnIndex(CardRecord.DH_Card_purchaseAmount)));
           record.setRemain(res.getDouble(res.getColumnIndex(CardRecord.DH_Card_remain)));
              record.setDiscountRate(res.getString(res.getColumnIndex(CardRecord.DH_Card_discountRate)));
            record.setUsageDate(res.getString(res.getColumnIndex(CardRecord.DH_Card_Date)));
            record.setDeviceNo(res.getString(res.getColumnIndex(CardRecord.DH_Card_DeviceNo)));
            record.setOperator(res.getString(res.getColumnIndexOrThrow(CardRecord.DH_Card_Operator)));
           record.setReportType(res.getInt(res.getColumnIndex(CardRecord.DH_Card_ReportType)));
         record.setRefundIndex(res.getInt(res.getColumnIndex(CardRecord.DH_Card_RefundIndex)));

        record.setHttpDateTime(res.getString(res.getColumnIndex(CardRecord.DH_Card_HttpDateTime)));
    record.setUploadMark(res.getInt(res.getColumnIndex(CardRecord.DH_Card_uploadMark)));
            // GlobalFunction.printTextByTag("search success");

        }else {

            GlobalFunction.printTextByTag("search fail");
        }
        res.close();


        return record;

    }

    public void removeAllPurchaseReport(){//删除所有消费记录

        SQLiteDatabase db=this.getWritableDatabase();
        db.delete(PurchaseReport.DH_TABLE_NAME, PurchaseReport.item_employeeID+"=?",new String[]{Operator.getIntance().getEmployeeID()});


    }




    public boolean removeAllReport(int reportType){//删除所有定额随机消费记录
     boolean stat=true;


        SQLiteDatabase db=this.getWritableDatabase();
        db.delete(CardRecord.DH_TABLE_NAME, CardRecord.DH_Card_ReportType + " = ?"+" AND "+ CardRecord.DH_Card_Operator+" = ?",new String[]{reportType+"", Operator.getIntance().getEmployeeID()});
//
//        Cursor res=db.rawQuery("delete from "+CardRecord.DH_TABLE_NAME + " WHERE "+CardRecord.DH_Card_ReportType+"="+reportType,null);
//         stat=res.moveToFirst();
//
//
//        if (stat){
//
//
//            res.close();
//
//        }
        return stat;


    }

    public void removeAllGood(){//删除所有定额随机消费记录
        boolean stat=true;


        SQLiteDatabase db=this.getWritableDatabase();
        db.delete(Good.DH_TABLE_NAME,null,null);


    }


   void deleteItem(String itemNo){//删除商品列表的某个商品
        SQLiteDatabase db=this.getWritableDatabase();


     db.delete(MenuItemTB.DH_TABLE_NAME, MenuItemTB.item_ID+"=?",new String[]{itemNo});


    }


    public ArrayList<CardRecord> getCardReport(String operator, int reportType) {
        SQLiteDatabase db=this.getReadableDatabase();
        Cursor res=db.rawQuery("select * from "+ CardRecord.DH_TABLE_NAME+ " WHERE "+ CardRecord.DH_Card_ReportType+" ="+String.valueOf(reportType)
                + " AND " + CardRecord.DH_Card_Operator + "=" + Operator.getIntance().getEmployeeID(),null);
        res.moveToFirst();
        ArrayList<CardRecord>recordList=new ArrayList<>();

        while (!res.isAfterLast()){
            CardRecord record=new CardRecord(res.getString(res.getColumnIndex(CardRecord.DH_Card_No)))  ;
            record.setId(res.getInt(res.getColumnIndex(CardRecord.DH_Card_ID)));

            record.setUsageCount(res.getInt(res.getColumnIndex(CardRecord.DH_Card_UsageCount)));
            record.setPurchaseAmount(res.getDouble(res.getColumnIndex(CardRecord.DH_Card_purchaseAmount)));
            record.setRemain(res.getDouble(res.getColumnIndex(CardRecord.DH_Card_remain)));
            record.setDiscountRate(res.getString(res.getColumnIndex(CardRecord.DH_Card_discountRate)));
            record.setUsageDate(res.getString(res.getColumnIndex(CardRecord.DH_Card_Date)));
            record.setDeviceNo(res.getString(res.getColumnIndex(CardRecord.DH_Card_DeviceNo)));
            record.setOperator(res.getString(res.getColumnIndexOrThrow(CardRecord.DH_Card_Operator)));
            record.setReportType(res.getInt(res.getColumnIndex(CardRecord.DH_Card_ReportType)));
            record.setRefundIndex(res.getInt(res.getColumnIndex(CardRecord.DH_Card_RefundIndex)));
            record.setCardName(res.getString(res.getColumnIndex(CardRecord.DH_Card_Name)));
            record.setHttpDateTime(res.getString(res.getColumnIndex(CardRecord.DH_Card_HttpDateTime)));
            record.setUploadMark(res.getInt(res.getColumnIndex(CardRecord.DH_Card_uploadMark)));
            recordList.add(record);



            res.moveToNext();

        }
        res.close();

        return recordList;

    }

    public ArrayList<CardRecord> getCardReport(int reportType, String startDate, String endDate) {
        SQLiteDatabase db=this.getReadableDatabase();
        Cursor res=db.rawQuery("select * from "+ CardRecord.DH_TABLE_NAME+ " WHERE "+ CardRecord.DH_Card_ReportType+" ="+String.valueOf(reportType)
                + " AND " + CardRecord.DH_Card_Operator + "=" + Operator.getIntance().getEmployeeID() + " AND " + CardRecord.DH_Card_Date
                +" BETWEEN ?" + " AND ? ",new String[]{startDate,endDate});
        res.moveToFirst();
        ArrayList<CardRecord>recordList=new ArrayList<>();

        while (!res.isAfterLast()){
            CardRecord record=new CardRecord(res.getString(res.getColumnIndex(CardRecord.DH_Card_No)))  ;
            record.setId(res.getInt(res.getColumnIndex(CardRecord.DH_Card_ID)));

            record.setUsageCount(res.getInt(res.getColumnIndex(CardRecord.DH_Card_UsageCount)));
            record.setPurchaseAmount(res.getDouble(res.getColumnIndex(CardRecord.DH_Card_purchaseAmount)));
            record.setRemain(res.getDouble(res.getColumnIndex(CardRecord.DH_Card_remain)));
            record.setDiscountRate(res.getString(res.getColumnIndex(CardRecord.DH_Card_discountRate)));
            record.setUsageDate(res.getString(res.getColumnIndex(CardRecord.DH_Card_Date)));
            record.setDeviceNo(res.getString(res.getColumnIndex(CardRecord.DH_Card_DeviceNo)));
            record.setOperator(res.getString(res.getColumnIndexOrThrow(CardRecord.DH_Card_Operator)));
            record.setReportType(res.getInt(res.getColumnIndex(CardRecord.DH_Card_ReportType)));
            record.setRefundIndex(res.getInt(res.getColumnIndex(CardRecord.DH_Card_RefundIndex)));
            record.setCardName(res.getString(res.getColumnIndex(CardRecord.DH_Card_Name)));
            record.setHttpDateTime(res.getString(res.getColumnIndex(CardRecord.DH_Card_HttpDateTime)));
            record.setUploadMark(res.getInt(res.getColumnIndex(CardRecord.DH_Card_uploadMark)));
            recordList.add(record);



            res.moveToNext();

        }
        res.close();

        return recordList;

    }


    public ArrayList<CardRecord> getCardReport(int uploadmark) {
        SQLiteDatabase db=this.getReadableDatabase();
        Cursor res=db.rawQuery("select * from "+ CardRecord.DH_TABLE_NAME+ " WHERE "+ CardRecord.DH_Card_uploadMark+" ="+String.valueOf(uploadmark)
                + " AND " + CardRecord.DH_Card_Operator + "=" + Operator.getIntance().getEmployeeID(),null);
        res.moveToFirst();
        ArrayList<CardRecord>recordList=new ArrayList<>();

        while (!res.isAfterLast()){
            CardRecord record=new CardRecord(res.getString(res.getColumnIndex(CardRecord.DH_Card_No)))  ;
            record.setId(res.getInt(res.getColumnIndex(CardRecord.DH_Card_ID)));

            record.setUsageCount(res.getInt(res.getColumnIndex(CardRecord.DH_Card_UsageCount)));
            record.setPurchaseAmount(res.getDouble(res.getColumnIndex(CardRecord.DH_Card_purchaseAmount)));
            record.setRemain(res.getDouble(res.getColumnIndex(CardRecord.DH_Card_remain)));
            record.setDiscountRate(res.getString(res.getColumnIndex(CardRecord.DH_Card_discountRate)));
            record.setUsageDate(res.getString(res.getColumnIndex(CardRecord.DH_Card_Date)));
            record.setDeviceNo(res.getString(res.getColumnIndex(CardRecord.DH_Card_DeviceNo)));
            record.setOperator(res.getString(res.getColumnIndexOrThrow(CardRecord.DH_Card_Operator)));
            record.setReportType(res.getInt(res.getColumnIndex(CardRecord.DH_Card_ReportType)));
            record.setRefundIndex(res.getInt(res.getColumnIndex(CardRecord.DH_Card_RefundIndex)));
            record.setCardName(res.getString(res.getColumnIndex(CardRecord.DH_Card_Name)));
            record.setHttpDateTime(res.getString(res.getColumnIndex(CardRecord.DH_Card_HttpDateTime)));
            record.setUploadMark(res.getInt(res.getColumnIndex(CardRecord.DH_Card_uploadMark)));
            recordList.add(record);



            res.moveToNext();

        }
        res.close();

        return recordList;

    }





    ArrayList<PurchasedItemTB.Item> getPurchasedItemHistory() {
        SQLiteDatabase db=this.getReadableDatabase();
        Cursor res=db.rawQuery("select * from "+ PurchasedItemTB.DH_TABLE_NAME  + " AND " + PurchaseReport.item_employeeID + "=" + Operator.getIntance().getEmployeeID() ,null);
        res.moveToFirst();
        ArrayList<PurchasedItemTB.Item>recordList=new ArrayList<>();

        while (!res.isAfterLast()){

            PurchasedItemTB.Item item=new PurchasedItemTB.Item(res.getString(res.getColumnIndex(PurchasedItemTB.item_ID)));
            item.card_ID=res.getString(res.getColumnIndex(PurchasedItemTB.item_CardID));
            item.item_TicketID=res.getString(res.getColumnIndex(PurchasedItemTB.item_TicketID));
            item.item_name=res.getString(res.getColumnIndex(PurchasedItemTB.item_name));
            item.item_amount=res.getInt(res.getColumnIndex(PurchasedItemTB.item__amount));
            item.item_payMode=res.getInt(res.getColumnIndex(PurchasedItemTB.item_payMode));
            item.saleCost=res.getInt(res.getColumnIndex(PurchasedItemTB.item_SaleCost));
            item.item_purchDate=res.getString(res.getColumnIndex(PurchasedItemTB.item_purchDate));
            recordList.add(item);



            res.moveToNext();

        }
        res.close();

        return recordList;

    }

   public ArrayList<PurchaseReport> getPurchasedReportBaseOnPurchaseType(Integer type) {
        SQLiteDatabase db=this.getReadableDatabase();
        Cursor res=db.rawQuery("select * from "+ PurchaseReport.DH_TABLE_NAME+" WHERE " + PurchaseReport.item_payMode+" ="+String.valueOf(type) + " AND "
                + PurchaseReport.item_employeeID + "=" + Operator.getIntance().getEmployeeID(),null);



        res.moveToFirst();
        ArrayList<PurchaseReport>recordList=new ArrayList<>();

        while (!res.isAfterLast()){

            PurchaseReport item=new PurchaseReport();
            item.setCommodityID( res.getString(res.getColumnIndex(PurchaseReport.item_commodityID)));
            item.setVipCardID(res.getString(res.getColumnIndex(PurchaseReport.item_vipCardId)));
            item.setTicketID(res.getString(res.getColumnIndex(PurchaseReport.item_ticketID)));
            item.setCommodityName(res.getString(res.getColumnIndex(PurchaseReport.item_commodityName)));
            item.setVolume(res.getInt(res.getColumnIndex(PurchaseReport.item_volume)));
            item.setPayMode(res.getInt(res.getColumnIndex(PurchaseReport.item_payMode)));
            item.setSaleCost(res.getString(res.getColumnIndex(PurchaseReport.item_saleCost)));
            item.setDateTime(res.getString(res.getColumnIndex(PurchaseReport.item_dateTime)));
            item.setDate(res.getString(res.getColumnIndex(PurchaseReport.item_date)));
            item.setCost(res.getString(res.getColumnIndex(PurchaseReport.item_cost)));
            item.setUploadMark(res.getInt(res.getColumnIndex(PurchaseReport.item_uploadMark)));
            item.setEmployeeID(res.getString(res.getColumnIndex(PurchaseReport.item_employeeID)));
            item.setPrimeCost(res.getString(res.getColumnIndex(PurchaseReport.item_primeCost)));
            item.setRestoredNum(res.getInt(res.getColumnIndex(PurchaseReport.item_restoredNum)));
            item.setDisCount(res.getString(res.getColumnIndex(PurchaseReport.item_discount)));
            item.setDateTimeCopy(res.getString(res.getColumnIndex(PurchaseReport.item_dateTimeCopy)));
            recordList.add(item);



            res.moveToNext();

        }
        res.close();

        return recordList;

    }



    ArrayList<PurchasedItemTB.Item> getPurchasedItemHistoryBaseOnPurchaseType(Integer type) {
        SQLiteDatabase db=this.getReadableDatabase();
        Cursor res=db.rawQuery("select * from "+ PurchasedItemTB.DH_TABLE_NAME+" WHERE " + PurchasedItemTB.item_payMode+" ="+String.valueOf(type)
                + " AND " + PurchaseReport.item_employeeID + "=" + Operator.getIntance().getEmployeeID(),null);



        res.moveToFirst();
        ArrayList<PurchasedItemTB.Item>recordList=new ArrayList<>();

        while (!res.isAfterLast()){

            PurchasedItemTB.Item item=new PurchasedItemTB.Item(res.getString(res.getColumnIndex(PurchasedItemTB.item_ID)));
            item.card_ID=res.getString(res.getColumnIndex(PurchasedItemTB.item_CardID));
            item.item_TicketID=res.getString(res.getColumnIndex(PurchasedItemTB.item_TicketID));
            item.item_name=res.getString(res.getColumnIndex(PurchasedItemTB.item_name));
            item.item_amount=res.getInt(res.getColumnIndex(PurchasedItemTB.item__amount));
            item.item_payMode=res.getInt(res.getColumnIndex(PurchasedItemTB.item_payMode));
            item.saleCost=res.getInt(res.getColumnIndex(PurchasedItemTB.item_SaleCost));
            item.item_purchDate=res.getString(res.getColumnIndex(PurchasedItemTB.item_purchDate));
            recordList.add(item);



            res.moveToNext();

        }
        res.close();

        return recordList;

    }


      ArrayList<RefundGoodTB.Item> getRefundedItemList(PurchaseReport purchaseItem) {
        SQLiteDatabase db=this.getReadableDatabase();
        Cursor res=db.query(RefundGoodTB.DH_TABLE_NAME,null,  RefundGoodTB.item_TicketID + " = ?" +" AND "+ RefundGoodTB.item_ID+" = ?"+ " AND "+ RefundGoodTB.item_payMode+
                 " = ?" + " AND " + RefundGoodTB.item_EmployeID+ " = ?" , new String[]{purchaseItem.getTicketID(),purchaseItem.getCommodityID(),String.valueOf(purchaseItem.getPayMode()), Operator.getIntance().getEmployeeID() },null,null,null);

        res.moveToFirst();

        ArrayList<RefundGoodTB.Item>recordList=new ArrayList<>();
        while (!res.isAfterLast()){

//    GlobalFunction.printTextByTag("query "+purchaseItem.item_TicketID+" "+purchaseItem.item_ID);


            RefundGoodTB.Item item=  new RefundGoodTB.Item(res.getString(res.getColumnIndex(RefundGoodTB.item_ID)));
           // item.card_ID=res.getString(res.getColumnIndex(PurchasedItemTB.item_CardID));
            item.item_TicketID=res.getString(res.getColumnIndex(RefundGoodTB.item_TicketID));
            item.item_name=res.getString(res.getColumnIndex(RefundGoodTB.item_name));
            item.item_amount=res.getInt(res.getColumnIndex(RefundGoodTB.item__amount));
            item.item_payMode=res.getInt(res.getColumnIndex(RefundGoodTB.item_payMode));
            item.saleCost=res.getDouble(res.getColumnIndex(RefundGoodTB.item_SaleCost));
            item.item_purchDate=res.getString(res.getColumnIndex(RefundGoodTB.item_purchDate));
            item.item_refundDate=res.getString(res.getColumnIndex(RefundGoodTB.item_refundDate));

            recordList.add(item);

            res.moveToNext();

        }

        res.close();

        return recordList;

    }

    ArrayList<RefundGoodTB.Item> getAllRefundedItemListBaseOnType(int type){
        SQLiteDatabase db=this.getReadableDatabase();
        Cursor res=db.rawQuery("select * from "+ RefundGoodTB.DH_TABLE_NAME+" WHERE " + RefundGoodTB.item_payMode+" ="+String.valueOf(type)
                + " AND " + PurchaseReport.item_employeeID + "=" + Operator.getIntance().getEmployeeID(),null);



        res.moveToFirst();
        ArrayList<RefundGoodTB.Item>recordList=new ArrayList<>();

        while (!res.isAfterLast()){

            RefundGoodTB.Item item=new RefundGoodTB.Item(res.getString(res.getColumnIndex(PurchasedItemTB.item_ID)));
            item.card_ID=res.getString(res.getColumnIndex( PurchasedItemTB.item_CardID));
            item.item_TicketID=res.getString(res.getColumnIndex(PurchasedItemTB.item_TicketID));
            item.item_name=res.getString(res.getColumnIndex(PurchasedItemTB.item_name));
            item.item_amount=res.getInt(res.getColumnIndex(PurchasedItemTB.item__amount));
            item.item_payMode=res.getInt(res.getColumnIndex(PurchasedItemTB.item_payMode));
            item.saleCost=res.getDouble(res.getColumnIndex(PurchasedItemTB.item_SaleCost));
            item.item_purchDate=res.getString(res.getColumnIndex(PurchasedItemTB.item_purchDate));
            item.item_refundDate=res.getString(res.getColumnIndex(RefundGoodTB.item_refundDate));
            item.purchaseVolume=res.getInt(res.getColumnIndex(RefundGoodTB.item_PurchaseVolume));
            item.employeeID=res.getString(res.getColumnIndex(RefundGoodTB.item_EmployeID));
            item.restoredNum=res.getInt(res.getColumnIndex(RefundGoodTB.item_restoredNum));
            item.httpDateTime=res.getString(res.getColumnIndex(RefundGoodTB.item_httpDateTimeCopy));
            item.primeCost=res.getString(res.getColumnIndex(RefundGoodTB.item_PrimeCost));
            item.discountRate=res.getString(res.getColumnIndex(RefundGoodTB.item_Discount));
            item.uploadMark=res.getInt(res.getColumnIndex(RefundGoodTB.item_uploadMark));

            recordList.add(item);



            res.moveToNext();

        }
        res.close();


    return recordList;

    }

   public int getCardReportRefundIndex(CardRecord card){
       SQLiteDatabase db=this.getReadableDatabase();
       Cursor res=db.rawQuery("select * from "+ CardRecord.DH_TABLE_NAME+ " WHERE "+ CardRecord.DH_Card_No+" = "+card.getCardNo()
               + " AND " + CardRecord.DH_Card_UsageCount + " = " + card.getUsageCount() + " AND "  + CardRecord.DH_Card_Operator + "=" + Operator.getIntance().getEmployeeID(),null);
    GlobalFunction.printTextByTag(card.getCardNo()+":"+card.getUsageCount());
       if (res.moveToFirst()){
           GlobalFunction.printTextByTag("query succ");
       }else {
           GlobalFunction.printTextByTag("query fail");
       }
       CardRecord record=null;

       while (!res.isAfterLast()){
           record=new CardRecord(res.getString(res.getColumnIndex(CardRecord.DH_Card_No)))  ;
           record.setUsageCount(res.getInt(res.getColumnIndex(CardRecord.DH_Card_UsageCount)));
           record.setPurchaseAmount(res.getDouble(res.getColumnIndex(CardRecord.DH_Card_purchaseAmount)));
           record.setRemain(res.getDouble(res.getColumnIndex(CardRecord.DH_Card_remain)));
           record.setDiscountRate(res.getString(res.getColumnIndex(CardRecord.DH_Card_discountRate)));
           record.setUsageDate(res.getString(res.getColumnIndex(CardRecord.DH_Card_Date)));
           record.setDeviceNo(res.getString(res.getColumnIndex(CardRecord.DH_Card_DeviceNo)));
           record.setOperator(res.getString(res.getColumnIndexOrThrow(CardRecord.DH_Card_Operator)));
           record.setReportType(res.getInt(res.getColumnIndex(CardRecord.DH_Card_ReportType)));
           record.setRefundIndex(res.getInt(res.getColumnIndex(CardRecord.DH_Card_RefundIndex)));
           record.setCardName(res.getString(res.getColumnIndex(CardRecord.DH_Card_Name)));




           res.moveToNext();

       }
       res.close();

       return 0;






   }

 public    void updateCardRefundIndex(CardRecord cardRecord){

         SQLiteDatabase db=this.getReadableDatabase();
       cardRecord.setRefundIndex(1);//表示退过款

GlobalFunction.printTextByTag(Operator.getIntance().getEmployeeID());
    String query="UPDATE "+ CardRecord.DH_TABLE_NAME+ " SET "+ CardRecord. DH_Card_RefundIndex + "=1"+" WHERE "+ CardRecord.DH_Card_No+"="+cardRecord.getCardNo()
           +" AND "+ CardRecord.DH_Card_UsageCount + "="+cardRecord.getUsageCount() + " AND " + CardRecord.DH_Card_Operator + "=" + Operator.getIntance().getEmployeeID();
    db.execSQL(query);
db.close();
GlobalFunction.printTextByTag(cardRecord.getCardNo());
    }



    ArrayList<Good> getAllMenuItem(){//获取商品列表所有商品

        ArrayList<Good> itemArrayList=new ArrayList<>();

        SQLiteDatabase db=this.getReadableDatabase();
        Cursor res=db.rawQuery("select * from "+ MenuItemTB.DH_TABLE_NAME ,null);
        res.moveToFirst();

        while (!res.isAfterLast()){

              Good item=new Good();

            item.setId(res.getString(res.getColumnIndex(MenuItemTB.item_ID)));
            item.setName(res.getString(res.getColumnIndex(MenuItemTB.item_ID)));
            item.setStoredNumber( String.valueOf(res.getInt(res.getColumnIndex(MenuItemTB.item_amount))));
            item.setPriceOut(String.valueOf(res.getInt(res.getColumnIndex(MenuItemTB.item_retailPrice))));


             itemArrayList.add(item);

        res.moveToNext();

        }
        res.close();


        return itemArrayList;

    }









    public ArrayList<CardTypeInfo>getCardTypeInfoList(){//获取卡类列表
        ArrayList<CardTypeInfo> cardTypeInfoArrayList=new ArrayList<>();
        SQLiteDatabase db=this.getReadableDatabase();

        Cursor res=db.rawQuery("select * from "+ CardTypeInfo.DH_TABLE_NAME ,null);
        res.moveToFirst();


        while (!res.isAfterLast()){

           CardTypeInfo cardTypeInfo=new CardTypeInfo();
            cardTypeInfo.id=res.getString(res.getColumnIndex(CardTypeInfo.DH_Card_ID));
            cardTypeInfo.name=res.getString(res.getColumnIndex(CardTypeInfo.DH_Card_Name));
            cardTypeInfo.discount=res.getString(res.getColumnIndex(CardTypeInfo.DH_Card_Discount));
            cardTypeInfo.addRatio=res.getString(res.getColumnIndex(CardTypeInfo.DH_Card_addRatio));

            cardTypeInfoArrayList.add(cardTypeInfo);


            res.moveToNext();

        }

        res.close();


        return cardTypeInfoArrayList;

    }
}
